package com.mes.report.service;

import com.yy.base.common.dao.Dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class StereoLibraryService {
	@Autowired
	private Dao dao;

	
	//获取看板数据
	public Map<String,Object> getReportPageData(){
		Map<String,Object> dataMap = new HashMap<String, Object>();

        /**
         * 查询作业工单列表
		 */
		List<Map<String, Object>> proJobList = getProJobList();
		/**
         * 查询立体库实时库存
		 */

		List<Map<String, Object>> proSevenList = getProSevenList();
		/**
         * 查询立体库实时库存占比
		 */

		List<Map<String, Object>> proExceptionList = getProExceptionList();
		dataMap.put("proJobList", proJobList);//获取作业工单列表数据
		dataMap.put("proSevenList", proSevenList);//获取七日产能情况列表
		dataMap.put("proExceptionList", proExceptionList);//获取异常情况汇报
        return dataMap;
	}

	/**
	 * 查询立体库实时库存
	 * @return
	 */
	public List<Map<String, Object>> getProJobList(){
		String sql = "select a.fzjcode, a.jobnum, b.code jobcodeing,b.efid,b.exe_date_from starttimes,b.exe_date_from ytime,(b.jd ||'%') jd from " +
				"(select ef.name fzjcode,ef.eam_facilities_id efid,count(job.code) jobnum " +
				"from eam_facilities ef " +
				"left join emeso_job_order_exe exe on ef.eam_facilities_id = exe.eam_facilities_id " +
				"LEFT join emeso_job_order job on job.emeso_job_order_id = exe.emeso_job_order_id " +
				"where ef.facility_type='FZJ' " +
				"and job.status='FINISHED' " +
				"GROUP BY ef.name,ef.eam_facilities_id ORDER BY ef.name) a " +
				"left join (select job.code ,ef.eam_facilities_id efid,ep.process_time,exe.exe_date_from,(ep.process_time/(date_part('day', (current_timestamp - exe.exe_date_from)) * 24 * 60 + date_part('minute', (current_timestamp - exe.exe_date_from))) * 100) jd " +
				"from emeso_job_order job,emeso_job_order_exe exe,eam_facilities ef,Emes_Process ep " +
				"where job.emeso_job_order_id = exe.emeso_job_order_id and ef.eam_facilities_id = exe.eam_facilities_id and ep.emes_process_id = job.emes_process_id and ef.facility_type='FZJ' and job.status='OK')b on a.efid=b.efid";
		return dao.query(sql);
	}
	/**
	 * 立体库实时库存查询
	 * @return
	 */
	public List<Map<String, Object>> getProExceptionList(){
		String sql = "select eam.name fzjcode,log.type,log.time intime,log.result,log.status status,ee.name ycname " +
				"from emeso_exception_log log,eam_facilities eam,emes_exception ee " +
				"where log.eam_facilities_id = eam.eam_facilities_id " +
				"and ee.emes_exception_id = log.emes_exception_id " +
				"and eam.facility_type='FZJ' order by eam.name";
		return dao.query(sql);
	}

	/**
	 * 立体库试试库存占比
	 * @return
	 */
	public List<Map<String, Object>> getProSevenList(){
		String sql = "select eam.name,eam.status " +
				"from eam_facilities eam " +
				"where eam.facility_type='FZJ' " +
				"GROUP BY eam.name,eam.status order by eam.name";
		return dao.query(sql);
	}

}
